<?php

	class Transform {

		private $families = array();
		public $source_path = '';
		public $output_path = '';
		public $filename = "usda_data.csv";
		public $dwc_filename = 'family.txt';
		public $species_filename = 'species.txt';
		public $vernacular_filename = 'vernacular.txt';
		public $geography_filename = 'geography.txt';
		public $meta_filename = 'meta.xml';
		public $eml_filename = 'eml.xml';
		public $zip_filename = 'usda_archive.zip';
		public $delimiter = "\t";
		public $cr = "\n";

		function __construct() {
			$this->source_path = BASE_PATH . "resources/";
			$this->output_path = BASE_PATH . "output/";
			$this->taxa = array();

			require_once 'phpexcel/Classes/PHPExcel.php';
			require_once 'phpexcel/Classes/PHPExcel/IOFactory.php';
			$this->objPHPExcel = new PHPExcel();
			$this->eml = new Eml();

   	}

		public function addTaxa( $rec ) {
			static $index = 1;			
			$parent = '';
			if ($rec->kingdom != '') {
				if (!isset( $this->taxa[$rec->kingdom] ) ) {
					$this->taxa[ $rec->kingdom ] = array(
						  "rank" => "kingdom"
						,	"parent" => $parent
						,	"value" => $rec->kingdom
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->kingdom . $parent;
			if ($rec->subkingdom != '') {
				if (!isset( $this->taxa[$rec->subkingdom . $parent] ) ) {
					$this->taxa[ $rec->subkingdom . $parent ] = array(
						  "rank" => "subkingdom"
						,	"parent" => $parent
						,	"value" => $rec->subkingdom
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->subkingdom . $parent;
			if ($rec->superdivision != '') {
				if (!isset( $this->taxa[$rec->superdivision . $parent] ) ) {
					$this->taxa[ $rec->superdivision . $parent ] = array(
						  "rank" => "superdivision"
						,	"parent" => $parent
						,	"value" => $rec->superdivision
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->superdivision . $parent;
			if (trim($rec->division) != '') {
				if (!isset( $this->taxa[$rec->division . $parent] ) ) {
					$this->taxa[ $rec->division . $parent ] = array(
						  "rank" => "division"
						,	"parent" => $parent
						,	"value" => $rec->division
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->division . $parent;
			if ($rec->subdivision != '') {
				if (!isset( $this->taxa[$rec->subdivision . $parent] ) ) {
					$this->taxa[ $rec->subdivision . $parent ] = array(
						  "rank" => "subdivision"
						,	"parent" => $parent
						,	"value" => $rec->subdivision
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->subdivision . $parent;
			if ($rec->class != '') {
				if (!isset( $this->taxa[$rec->class . $parent] ) ) {
					$this->taxa[ $rec->class . $parent ] = array(
						  "rank" => "class"
						,	"parent" => $parent
						,	"value" => $rec->class
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->class . $parent;
			if ($rec->subclass != '') {
				if (!isset( $this->taxa[$rec->subclass . $parent] ) ) {
					$this->taxa[ $rec->subclass . $parent ] = array(
						  "rank" => "subclass"
						,	"parent" => $parent
						,	"value" => $rec->subclass
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->subclass . $parent;
			if ($rec->order != '') {
				if (!isset( $this->taxa[$rec->order . $parent] ) ) {
					$this->taxa[ $rec->order . $parent ] = array(
						  "rank" => "order"
						,	"parent" => $parent
						,	"value" => $rec->order
						,	"index" => $index++
						,	"common" => ""
					);
				}
			}

			$parent = $rec->order . $parent;
			if ($rec->family != '') {
				if (!isset( $this->taxa[$rec->family . $parent] ) ) {
					$this->taxa[ $rec->family . $parent ] = array(
						  "rank" => "family"
						,	"parent" => $parent
						,	"value" => $rec->family
						,	"index" => $rec->family_symb
						, "common" => str_replace(" family", "", $rec->family_common)
					);
				}
			}

		}
		
		public function createHigherTaxa() {
			if (file_exists( $this->source_path . $this->filename ) ) {
				$fp_source = fopen($this->source_path . $this->filename, "r");
				$data = fgetcsv($fp_source, 10000, ","); // Skip first line
				while (($data = fgetcsv($fp_source, 10000, ",")) !== FALSE) {
					$tmp->kingdom = $data[38];
					$tmp->subkingdom = $data[37];
					$tmp->superdivision = $data[36];
					$tmp->division = $data[35];
					$tmp->subdivision = $data[34];
					$tmp->class = $data[33];
					$tmp->subclass = $data[32];
					$tmp->order = $data[31];
					$tmp->family = $data[28];
					$tmp->family_symb = $data[29];
					$tmp->family_common = $data[30];
					$this->addTaxa( $tmp );
				}				
				
				$fp_species = fopen($this->output_path . $this->species_filename, "w");
				fputs($fp_species, implode( $this->delimiter, array( 'TaxonID', 'ScientificName', 'HigherTaxonID', 'TaxonRank', 'ITIS ID', "Accepted Name Usage ID", "Taxonomic Status" ))  . $this->cr );
				$fp_vernacular = fopen($this->output_path . $this->vernacular_filename, "w");
				fputs($fp_vernacular, implode( $this->delimiter, array( 'TaxonID', 'ScientificName', 'Common Name', 'Language'))  . $this->cr );
				foreach( $this->taxa as $rec) {					
					fputs($fp_species, implode( $this->delimiter, array( $rec["index"], $rec["value"], @$this->taxa[$rec["parent"]]["index"], $rec["rank"] ))  . $this->cr );
					if ($rec["common"] != '') {
						// Save common name to file
						fputs($fp_vernacular, implode( $this->delimiter, array( $rec["index"], $rec["value"], $rec["common"], "en")) . $this->cr );
					}
				}
			}
		}
		
		public function createCSV() {
			if (file_exists( $this->source_path . $this->filename ) ) {
				$fp_source = fopen($this->source_path . $this->filename, "r");
				$data = fgetcsv($fp_source, 10000, ","); // Skip first line

				$fp_species = fopen($this->output_path . $this->species_filename, "a");
				$fp_vernacular = fopen($this->output_path . $this->vernacular_filename, "a");
				$fp_geography = fopen($this->output_path . $this->geography_filename, "w");
				fputs($fp_geography, implode( $this->delimiter, array( 'Taxon ID', 'Country Code', 'Location ID', 'Locality', 'Occurrence Status'))  . $this->cr );

				while (($data = fgetcsv($fp_source, 100000, ",")) !== FALSE) {
					
					if ($data[1] == '') {
						// Accepted Species
						fputs($fp_species, implode( $this->delimiter, array( $data[0], $data[3], $data[28], "ScientificName", $data[39] ) )  . $this->cr );
						
						if ($data[23] != '') {
							// Save common name to file
							fputs($fp_vernacular, implode( $this->delimiter, array( $data[0], $data[3], $data[23], "en")) . $this->cr );
						}
						
						// Write Geography regions to file.
						if ($data[25] != '') {
							$tmp = explode("), ", $data[25]); // Split between groups like US (...), CA (...)
							if (!is_array($tmp)) {
								// Only one group so strip end and put in the array
								$tmp[] = str_replace(")", "", $data[21]);
							}
							if( is_array($tmp) ) {
							foreach( $tmp as $group ) {
								$t2 = explode(" (", $group);
								$country = $t2[0];
								$states = explode(",", $t2[1]);
								if (is_array($states)) {
								foreach( $states as $state ) {
									$state = trim(str_replace(")", "", $state));
									fputs($fp_geography, implode( $this->delimiter, array( $data[0], "ISO_3166-2:" . substr($country, 0,2), substr($country, 0,2) . "-" . $state, $state, "present")) . $this->cr );
								}
								}
							}
							}
						}

					} else {
						// Synonym
						fputs($fp_species, implode( $this->delimiter, array( $data[1], $data[3], $data[28], "ScientificName", $data[39], $data[0], "synonym" ) )  . $this->cr );
					}

				}
				fclose($fp_species);
				fclose($fp_source);
		
				return( true );
			} else {
				return( false );
			}
		}
		
		private function openExcel() {
			// Read from Excel2007 (.xlsx) template
			// Read from Excel2005 (.xls) template
			$this->objReader = PHPExcel_IOFactory::createReader('Excel5');
			$this->objPHPExcel = $this->objReader->load( $this->sourcefile );
		}
		
		public function convert2csv() {
			$this->openExcel();
			
			$this->objPHPExcel->setActiveSheetIndex(2);
			$this->outfile = md5(mktime()) . ".csv";
			$this->objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'CSV')
					->setDelimiter(',')
					->setEnclosure('')
					->setLineEnding("\r\n")
					->setSheetIndex(2)
					->save( $this->output_path . $this->outfile );
		}
		
		public function createMeta() {
			copy($this->source_path . 'meta.xml', $this->output_path . 'meta.xml');
		}
	
		public function createEml() {
			$this->openExcel();
	
			$wk = $this->objPHPExcel->setActiveSheetIndex(1);
			#Basic Metadata	
			$this->eml->set('ResourceType', $wk->getCell('B3')->getValue() );
			$this->eml->set('ResourceTitle', $wk->getCell('B4')->getValue() );
			$this->eml->set('SourceDatabaseName', $wk->getCell('B5')->getValue() );
			$this->eml->set('DatabaseVersion', $wk->getCell('B6')->getValue() );
			$this->eml->set('ResourceLanguage', $wk->getCell('B7')->getValue() );
			$this->eml->set('MetadataLanguage', $wk->getCell('B8')->getValue() );
			$this->eml->set('CharacterEncoding', $wk->getCell('B9')->getValue() );
			$this->eml->set('Description', $wk->getCell('B10')->getValue() );

			#Research Project
			$this->eml->set('Title', $wk->getCell('B3')->getValue() );
			$this->eml->set('ProjectLeadOrganisation', $wk->getCell('B4')->getValue() );
			$this->eml->set('Abstract', $wk->getCell('B5')->getValue() );
			$this->eml->set('Funding', $wk->getCell('B6')->getValue() );
			$this->eml->set('StudyAreaDescription', $wk->getCell('B7')->getValue() );
			$this->eml->set('Design Description', $wk->getCell('B8')->getValue() );

			#People and Organisations
			$this->eml->set('MetadataAuthor.Role', $wk->getCell('B16')->getValue() );
			$this->eml->set('MetadataAuthor.FirstName', $wk->getCell('B17')->getValue() );
			$this->eml->set('MetadataAuthor.LastName', $wk->getCell('B18')->getValue() );
			$this->eml->set('MetadataAuthor.Organisation', $wk->getCell('B19')->getValue() );
			$this->eml->set('MetadataAuthor.Phone', $wk->getCell('B20')->getValue() );
			$this->eml->set('MetadataAuthor.Email', $wk->getCell('B21')->getValue() );
			$this->eml->set('MetadataAuthor.Homepage', $wk->getCell('B22')->getValue() );
			$this->eml->set('MetadataAuthor.Address', $wk->getCell('B23')->getValue() );
			$this->eml->set('MetadataAuthor.PostalCode', $wk->getCell('B24')->getValue() );
			$this->eml->set('MetadataAuthor.City', $wk->getCell('B25')->getValue() );
			$this->eml->set('MetadataAuthor.Province', $wk->getCell('B26')->getValue() );
			$this->eml->set('MetadataAuthor.Country', $wk->getCell('B27')->getValue() );

			#People and Organisations
			$this->eml->set('ResourceCreator.Role', $wk->getCell('C16')->getValue() );
			$this->eml->set('ResourceCreator.FirstName', $wk->getCell('C17')->getValue() );
			$this->eml->set('ResourceCreator.LastName', $wk->getCell('C18')->getValue() );
			$this->eml->set('ResourceCreator.Organisation', $wk->getCell('C19')->getValue() );
			$this->eml->set('ResourceCreator.Phone', $wk->getCell('C20')->getValue() );
			$this->eml->set('ResourceCreator.Email', $wk->getCell('C21')->getValue() );
			$this->eml->set('ResourceCreator.Homepage', $wk->getCell('C22')->getValue() );
			$this->eml->set('ResourceCreator.Address', $wk->getCell('C23')->getValue() );
			$this->eml->set('ResourceCreator.PostalCode', $wk->getCell('C24')->getValue() );
			$this->eml->set('ResourceCreator.City', $wk->getCell('C25')->getValue() );
			$this->eml->set('ResourceCreator.Province', $wk->getCell('C26')->getValue() );
			$this->eml->set('ResourceCreator.Country', $wk->getCell('C27')->getValue() );

			#People and Organisations
			$this->eml->set('AssociatedParty.Role', $wk->getCell('D16')->getValue() );
			$this->eml->set('AssociatedParty.FirstName', $wk->getCell('D17')->getValue() );
			$this->eml->set('AssociatedParty.LastName', $wk->getCell('D18')->getValue() );
			$this->eml->set('AssociatedParty.Organisation', $wk->getCell('D19')->getValue() );
			$this->eml->set('AssociatedParty.Phone', $wk->getCell('D20')->getValue() );
			$this->eml->set('AssociatedParty.Email', $wk->getCell('D21')->getValue() );
			$this->eml->set('AssociatedParty.Homepage', $wk->getCell('D22')->getValue() );
			$this->eml->set('AssociatedParty.Address', $wk->getCell('D23')->getValue() );
			$this->eml->set('AssociatedParty.PostalCode', $wk->getCell('D24')->getValue() );
			$this->eml->set('AssociatedParty.City', $wk->getCell('D25')->getValue() );
			$this->eml->set('AssociatedParty.Province', $wk->getCell('D26')->getValue() );
			$this->eml->set('AssociatedParty.Country', $wk->getCell('D27')->getValue() );

			#Geographical,Taxonomic and General Keywords	
			$this->eml->set('RegionalScope', $wk->getCell('B31')->getValue() );
			$this->eml->set('TaxonomicKeywords', $wk->getCell('B32')->getValue() );
			$this->eml->set('GeneralKeywords', $wk->getCell('B33')->getValue() );
			
			$this->eml->set('BoundingBoxLeftLat', $wk->getCell('D32')->getValue() );
			$this->eml->set('BoundingBoxLeftLong', $wk->getCell('E32')->getValue() );
			$this->eml->set('BoundingBoxRightLat', $wk->getCell('D33')->getValue() );
			$this->eml->set('BoundingBoxRightLong', $wk->getCell('E33')->getValue() );

			#Methods	
			$this->eml->set('StudyExtent', $wk->getCell('D38')->getValue() );
			$this->eml->set('Sampling', $wk->getCell('D39')->getValue() );
			$this->eml->set('BoundingBoxRightLong', $wk->getCell('E33')->getValue() );

			#Method Steps
//

			#IPR
			$this->eml->set('Purpose', $wk->getCell('B50')->getValue() );
			$this->eml->set('Maintenance', $wk->getCell('B51')->getValue() );
			$this->eml->set('IPRRights', $wk->getCell('B52')->getValue() );
			$this->eml->set('BibliographicCitation', $wk->getCell('B53')->getValue() );
			$this->eml->set('References(1)', $wk->getCell('B54')->getValue() );
			$this->eml->set('References(2)', $wk->getCell('B55')->getValue() );
			$this->eml->set('References(3)', $wk->getCell('B56')->getValue() );

			#Collections-related Metadata
			$this->eml->set('CollectionName', $wk->getCell('B61')->getValue() );
			$this->eml->set('CollectionIdentifier', $wk->getCell('B62')->getValue() );
			$this->eml->set('ParentCollectionIdentifier', $wk->getCell('B63')->getValue() );
			$this->eml->set('FormationPeriod', $wk->getCell('B64')->getValue() );
			$this->eml->set('LivingTimePeriod', $wk->getCell('B65')->getValue() );
			$this->eml->set('SpecimenPreservationMethod', $wk->getCell('B66')->getValue() );
			
//			@unlink($this->output_path . $this->eml_filename);
//			$fp = fopen( $this->output_path . $this->eml_filename, "w" );
//			fwrite( $fp, $tpl );
//			fclose( $fp );
		}

		public function zipArchive() {

			$filename = $this->output_path . $this->zip_filename;
			
			$zip = new zip_file( $filename );
			$zip->set_options(array('inmemory' => 1, 'recurse' => 0, 'storepaths' => 0));
	
			$zip->add_files($this->output_path . $this->eml_filename, $this->eml_filename);
			$zip->add_files($this->output_path . $this->meta_filename, $this->meta_filename);
			$zip->add_files($this->output_path . $this->dwc_filename, $this->dwc_filename);
			$zip->add_files($this->output_path . $this->species_filename, $this->species_filename);
			$zip->add_files($this->output_path . $this->vernacular_filename, $this->vernacular_filename);
			$zip->add_files($this->output_path . $this->geography_filename, $this->geography_filename);

			$zip->create_archive();
			$zip->save_file( $filename );
			
			@unlink($this->source_path . $this->filename); // Original CSV file downloaded
			@unlink($this->output_path . $this->species_filename);
			@unlink($this->output_path . $this->vernacular_filename);
			@unlink($this->output_path . $this->geography_filename);
			@unlink($this->output_path . $this->eml_filename);
			@unlink($this->output_path . $this->meta_filename);
			
		}
		
	}

?>